/*********************************************************************************
 * 회원 쿼리 시작
 *********************************************************************************/
-- 회원 (member)
drop table member cascade constraints purge;
create table member (
	memberId varchar2(30) primary key,
	memberName varchar2(30) not null,
	password varchar2(30) not null,
	email varchar2(50) not null,
	joinDate date not null,
	isAdmin varchar2(10) not null
);

/*********************************************************************************
 * 커뮤니티 쿼리 시작
 *********************************************************************************/
-- 게시판 리스트 (boardList)
drop table boardList cascade constraints purge;
create table boardList (
	boardId number primary key,
	boardName varchar2(50) not null,
	regDate date not null
);

-- 게시물 시퀀스
drop sequence board_seq;
create sequence board_seq;

-- 게시물 (board)
drop table board cascade constraints purge;
create table board (
	contentId number primary key,
	groupId number not null,
	groupPosition number not null,
	groupDepth number not null,
	category varchar2(20),
	title varchar2(100) not null,
	content varchar2(4000) not null,
	regDate date not null,
	modDate date not null,
	hits number not null,
	isNotice varchar2(10) not null,
	commentHits number not null,
	boardId number not null,
	memberId varchar2(30) not null,
	locationId number not null,
	foreign key(locationId) REFERENCES travelzone(locationId),
	foreign key(boardId) REFERENCES boardList(boardId) on delete cascade,
	foreign key(memberId) REFERENCES member(memberId)
);

-- 첨부파일 시퀀스
drop sequence attachment_seq;
create sequence attachment_seq;

-- 첨부파일 (file)
drop table attachment cascade constraints purge;
create table attachment (
	fileId number primary key,
	fileName varchar2(50) not null,
	filePath varchar2(50) not null,
	fileSize long not null,
	fileType varchar2(20) not null,
	download number not null,
	contentId number not null,
	foreign key(contentId) REFERENCES board(contentId)
);

select * from attachment

-- 댓글 시퀀스
drop sequence comments_seq;
create sequence comments_seq;

-- 댓글 (comments)
drop table comments cascade constraints purge;
create table comments (
	commentsId number primary key,
	comments varchar2(1000) not null,
	contentId number not null,
	memberId varchar2(30) not null,
	regDate date not null,
	modDate date not null,
	foreign key(contentId) REFERENCES board(contentId) on delete cascade,
	foreign key(memberId) REFERENCES member(memberId)
);

/*********************************************************************************
 * 여행 정보 쿼리 시작
 *********************************************************************************/
-- 지역 정보 (travelzone)
drop table travelzone cascade constraints purge;
create table travelzone (
	locationid number primary key,
	location varchar2(20) not null,
	zoneinfo varchar2(50) not null,
	zoneimg varchar2(50) not null
);

-- 여행지 정보 (travelinfo)
drop table travelinfo cascade constraints purge;
create table travelinfo (
	place varchar2(30) primary key,
	locationid number,
	pnumber varchar2(20) not null,
	address varchar2(50) not null,
	phomepage varchar2(30) not null,
	notice varchar2(400),
	information varchar2(4000) not null,
	traffic varchar2(50) not null,
	map varchar2(50) not null,
	foreign key(locationid) references travelzone(locationid)
);

-- 여행지 갤러리 (image)
drop table image cascade constraints purge;
create table image (
	place varchar2(30) not null,
	gallery varchar2(50) not null,
	foreign key(place) references travelinfo(place)
);

-- 주변정보_숙박 (vacancy)
drop table vacancy cascade constraints purge;
create table vacancy (
	stay varchar2(20),
	place varchar2(30),
	vnumber varchar2(20) not null,
	vhomepage varchar2(30),
	primary key(stay, place),
	foreign key(place) references travelinfo(place)
);

-- 주변정보_맛집 (famous restaurant(fr))
drop table fr cascade constraints purge;
create table fr ( 
	restaurant varchar2(20),
	place varchar2(30),
	fnumber varchar2(20) not null,
	fhomepage varchar2(30),
	primary key(restaurant, place),
	foreign key(place) references travelinfo(place)
);

-- 여행지 댓글 (travelcomment)
drop table travelcomment cascade constraints purge;
create table travelcomment (
	place varchar2(30),
	memberid varchar2(30),
	content varchar2(4000) not null,
	recommend number default 0,
	primary key(place, memberid),
	foreign key(place) references travelinfo(place),
	foreign key(memberid) references member(memberid)
);

-- BoardList 에 넣어서 사용하세요.
INSERT INTO boardList VALUES (1, '여행중', sysdate);
INSERT INTO boardList VALUES (2, '여행후기', sysdate);
INSERT INTO boardList VALUES (3, '자유게시판', sysdate);
INSERT INTO boardList VALUES (4, '질문게시판', sysdate);

-- member 테이블에 넣어서 사용하세요.
INSERT INTO member VALUES('admin', '관리자', 'admin', 'admin@wetogether.com', sysdate, 'Y');
INSERT INTO member VALUES('java', '자바', '1234', 'java@sample.com', sysdate, 'N');


-- 지역
INSERT INTO travelzone VALUES(1, '서울특별시', '대한민국', '대한민국');
INSERT INTO travelzone VALUES(2, '경기도', '대한민국', '대한민국');
INSERT INTO travelzone VALUES(3, '강원도', '대한민국', '대한민국');
INSERT INTO travelzone VALUES(4, '충청북도', '대한민국', '대한민국');
INSERT INTO travelzone VALUES(5, '충청남도', '대한민국', '대한민국');
INSERT INTO travelzone VALUES(6, '전라북도', '대한민국', '대한민국');
INSERT INTO travelzone VALUES(7, '전라남도', '대한민국', '대한민국');
INSERT INTO travelzone VALUES(8, '경상북도', '대한민국', '대한민국');
INSERT INTO travelzone VALUES(9, '경상남도', '대한민국', '대한민국');
INSERT INTO travelzone VALUES(10, '제주도', '대한민국', '대한민국');

select * from board



SELECT board_seq.nextval FROM dual



SELECT MIN(groupPosition) FROM board 
		WHERE groupId = 20;
CREATE INDEX groupId_idx on board(groupId);
CREATE INDEX groupPosition_idx on board(groupPosition);
		